﻿using ClassEntities;
using Functions;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace DAOs
{
    public class StudentDAO
    {
        /// <summmary>
        /// DAO for tblSinhVien
        /// </summary>
        public tblSinhVien getSingletblSinhVien(int masv)
        {
            string sql;
            sql = String.Format("SELECT * FROM tblSinhVien WHERE stt = '{0}'", masv);
            DataTable dt = Database.returnDataTable(sql);
            tblSinhVien bean = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    int stt = int.Parse(row["stt"].ToString());
                    int idTruong = int.Parse(row["idTruong"].ToString());
                    string hoten = row["hoten"].ToString();
                    DateTime ngaysinh = (DateTime)(row["ngaysinh"]);
                    string diachi = row["diachi"].ToString();
                    string dienthoai = row["dienthoai"].ToString();
                    string email = row["email"].ToString();
                    string gioitinh = row["gioitinh"].ToString();
                    string pass = row["pass"].ToString();
                    string maSV = row["maSV"].ToString();
                    object imgSV = (object)(row["imgSV"]);
                    string enable = row["enable"].ToString();
                    string ghichu = row["ghichu"].ToString();
                    bean = new tblSinhVien(stt, idTruong, hoten, ngaysinh, diachi, dienthoai, email, gioitinh, pass, maSV, imgSV, enable, ghichu);
                }
            }
            return bean;
        }
        /// <summmary>
        /// DAO for Login
        /// </summary>
        public tblSinhVien SinhVienLogin(Hashtable hash)
        {
            string sql;
            sql = String.Format("SELECT * FROM tblSinhVien" + Database.prameter(hash));
            DataTable dt = Database.returnDataTable(sql);
            tblSinhVien bean = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    int stt = int.Parse(row["stt"].ToString());
                    int idTruong = int.Parse(row["idTruong"].ToString());
                    string hoten = row["hoten"].ToString();
                    DateTime ngaysinh = (DateTime)(row["ngaysinh"]);
                    string diachi = row["diachi"].ToString();
                    string dienthoai = row["dienthoai"].ToString();
                    string email = row["email"].ToString();
                    string gioitinh = row["gioitinh"].ToString();
                    string pass = row["pass"].ToString();
                    string maSV = row["maSV"].ToString();
                    object imgSV = (object)(row["imgSV"]);
                    string enable = row["enable"].ToString();
                    string ghichu = row["ghichu"].ToString();
                    bean = new tblSinhVien(stt, idTruong, hoten, ngaysinh, diachi, dienthoai, email, gioitinh, pass, maSV, imgSV, enable, ghichu);
                }
            }
            return bean;
        }
        /// <summmary>
        /// DAO for Truong
        /// </summary>
        public tblTruongHoc gettblTruongHoc(Hashtable hash)
        {
            string sql;
            sql = String.Format("SELECT * FROM tblTruongHoc" + Database.prameter(hash));
            DataTable dt = Database.returnDataTable(sql);
            tblTruongHoc bean = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    int stt = int.Parse(row["stt"].ToString());
                    object logo = (object)(row["logo"]);
                    string tenTruong = row["tenTruong"].ToString();
                    string diachi = row["diachi"].ToString();
                    string dienthoai = row["dienthoai"].ToString();
                    string fax = row["fax"].ToString();
                    string email = row["email"].ToString();
                    string mota = row["mota"].ToString();
                    string lat = row["lat"].ToString();
                    string longs = row["long"].ToString();
                    string enable = row["enable"].ToString();
                    bean = new tblTruongHoc(stt, logo, tenTruong, diachi, dienthoai, fax, email, mota, lat, longs, enable);
                }
            }
            return bean;
        }
        /// <summmary>
        /// DAO for tblMonHoc
        /// </summary>
        public List<tblMonHoc> gettblMonHoc(Hashtable hash)
        {
            string sql;
            sql = String.Format("SELECT * FROM tblMonHoc" + Database.prameter(hash));
            List<tblMonHoc> list = new List<tblMonHoc>();
            DataTable dt = Database.returnDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    int stt = int.Parse(row["stt"].ToString());
                    int idTruong = int.Parse(row["idTruong"].ToString());
                    string monhoc = row["monhoc"].ToString();
                    string soTC = row["soTC"].ToString();
                    string dsGiangVien = row["dsGiangVien"].ToString();
                    tblMonHoc bean = new tblMonHoc(stt, idTruong, monhoc, soTC, dsGiangVien);
                    list.Add(bean);
                }
            }
            return list;
        }
        /// <summmary>
        /// DAO for tblToChucThi
        /// </summary>
        public List<tblToChucThi> gettblToChucThi(Hashtable hash)
        {
            string sql;
            sql = String.Format("SELECT * FROM tblToChucThi" + Database.prameter(hash));
            List<tblToChucThi> list = new List<tblToChucThi>();
            DataTable dt = Database.returnDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    int stt = int.Parse(row["stt"].ToString());
                    int idLopHoc = int.Parse(row["idLopHoc"].ToString());
                    string dsDeThi = row["dsDeThi"].ToString();
                    DateTime ngaythi = (DateTime)(row["ngaythi"]);
                    string giothi = row["giothi"].ToString();
                    string dsGiamKhao = row["dsGiamKhao"].ToString();
                    string dsGiaoVienChamThi = row["dsGiaoVienChamThi"].ToString();
                    int idKyThi = int.Parse(row["idKyThi"].ToString());
                    tblToChucThi bean = new tblToChucThi(stt, idLopHoc, dsDeThi, ngaythi, giothi, dsGiamKhao, dsGiaoVienChamThi, idKyThi);
                    list.Add(bean);
                }
            }
            return list;
        }
        /// <summmary>
        /// DAO for tblKyThi
        /// </summary>
        public List<tblKyThi> gettblKyThi(Hashtable hash)
        {
            string sql;
            sql = String.Format("SELECT * FROM tblKyThi" + Database.prameter(hash));
            List<tblKyThi> list = new List<tblKyThi>();
            DataTable dt = Database.returnDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    int stt = int.Parse(row["stt"].ToString());
                    string tenKyThi = row["tenKyThi"].ToString();
                    int idTruong = int.Parse(row["idTruong"].ToString());
                    tblKyThi bean = new tblKyThi(stt, tenKyThi, idTruong);
                    list.Add(bean);
                }
            }
            return list;
        }
        /// <summmary>
        /// DAO for tblLopHoc
        /// </summary>
        public List<tblLopHoc> gettblLopHoc(Hashtable hash)
        {
            string sql;
            sql = String.Format("SELECT * FROM tblLopHoc" + Database.prameter(hash));
            List<tblLopHoc> list = new List<tblLopHoc>();
            DataTable dt = Database.returnDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    int stt = int.Parse(row["stt"].ToString());
                    int idKhoa = int.Parse(row["idKhoa"].ToString());
                    int idCTDT = int.Parse(row["idCTDT"].ToString());
                    string ten = row["ten"].ToString();
                    DateTime ngaydangki = (DateTime)(row["ngaydangki"]);
                    DateTime ngaykhaigiang = (DateTime)(row["ngaykhaigiang"]);
                    DateTime ngayketthuc = (DateTime)(row["ngayketthuc"]);
                    string isEnd = row["isEnd"].ToString();
                    string dsSinhVien = row["dsSinhVien"].ToString();
                    tblLopHoc bean = new tblLopHoc(stt, idKhoa, idCTDT, ten, ngaydangki, ngaykhaigiang, ngayketthuc, isEnd, dsSinhVien);
                    list.Add(bean);
                }
            }
            return list;
        }
        /// <summmary>
        /// DAO for tblDangKyLop
        /// </summary>
        public List<tblDangKyLop> gettblDangKyLop(Hashtable hash)
        {
            string sql;
            sql = String.Format("SELECT A.*, B.ten FROM tblDangKyLop A LEFT JOIN tblLopHoc B ON A.idLH = B.Stt " + Database.prameter(hash));
            List<tblDangKyLop> list = new List<tblDangKyLop>();
            DataTable dt = Database.returnDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    int stt = int.Parse(row["stt"].ToString());
                    int idLH = int.Parse(row["idLH"].ToString());
                    int idSV = int.Parse(row["idSV"].ToString());
                    string ten = row["ten"].ToString();
                    DateTime ngaydangky = (DateTime)(row["ngaydangky"]);
                    tblDangKyLop table = new tblDangKyLop(stt, idLH, idSV, ngaydangky, ten);
                    list.Add(table);
                }
            }
            return list;
        }
        /// <summmary>
        /// DAO for tblDeThi
        /// </summary>
        public List<tblDeThi> gettblDeThi(Hashtable hash)
        {
            string sql;
            sql = String.Format("SELECT * FROM tblDeThi" + Database.prameter(hash));
            List<tblDeThi> list = new List<tblDeThi>();
            DataTable dt = Database.returnDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    int stt = int.Parse(row["stt"].ToString());
                    string tenMH = row["tenMH"].ToString();
                    int idGiangVien = int.Parse(row["idGiangVien"].ToString());
                    string tenDeThi = row["tenDeThi"].ToString();
                    string thoigianlambai = row["thoigianlambai"].ToString();
                    int soluongcauhoi = int.Parse(row["soluongcauhoi"].ToString());
                    string diemdat = row["diemdat"].ToString();
                    string diemtoida = row["diemtoida"].ToString();
                    string ghichu = row["ghichu"].ToString();
                    string ghichusaukhithi = row["ghichusaukhithi"].ToString();
                    DateTime ngaytao = (DateTime)(row["ngaytao"]);
                    tblDeThi bean = new tblDeThi(stt, tenMH, idGiangVien, tenDeThi, thoigianlambai, soluongcauhoi, diemdat, diemtoida, ghichu, ghichusaukhithi, ngaytao);
                    list.Add(bean);

                }
            }
            return list;
        }
        /// <summmary>
        /// DAO for tblKetQuaThi
        /// </summary>
        public List<tblKetQuaThi> gettblKetQuaThi(int id)
        {
            string sql;
            sql = String.Format("Select  A.stt , A.diemthi,  A.ketqua,  C.idLopHoc, "
                + " B.tenMH, B.diemtoida, B.diemdat, B.thoigianlambai, B.ghichu, "
                + " C.ngaythi,  C.giothi,  D.tenKyThi from tblKetQuaThi A, tblDeThi B, tblToChucThi C, tblKyThi D "
                + "WHERE A.idDeThi = B.stt AND A.idToChucThi = C.stt AND C.idKyThi = D.stt AND A.idSinhVien = '{0}'", id);
            List<tblKetQuaThi> list = new List<tblKetQuaThi>();
            DataTable dt = Database.returnDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    int stt = int.Parse(row["stt"].ToString());
                    string diemthi = row["diemthi"].ToString();
                    string ketqua = row["ketqua"].ToString();
                    int idLopHoc = int.Parse(row["idLopHoc"].ToString());
                    string tenMH = row["tenMH"].ToString();
                    string thoigianlambai = row["thoigianlambai"].ToString();
                    string diemdat = row["diemdat"].ToString();
                    string diemtoida = row["diemtoida"].ToString();
                    string ghichu = row["ghichu"].ToString();
                    DateTime ngaythi = (DateTime)row["ngaythi"];
                    string giothi = row["giothi"].ToString();
                    string tenKyThi = row["tenKyThi"].ToString();

                    tblKetQuaThi bean = new tblKetQuaThi(stt, diemthi, ketqua, idLopHoc, tenMH, thoigianlambai, diemdat, diemtoida, ghichu, ngaythi, giothi, tenKyThi);
                    list.Add(bean);
                }
            }
            return list;
        }
        /// <summary>
        /// DAO for list CauHoiThi
        /// </summary>
        public List<tblCauHoiThi> gettblCauHoi(string tochucthiid, int idSinhvien)
        {
            string sql;
            sql = String.Format("SELECT * FROM tblToChucThi WHERE stt = '{0}'", tochucthiid);
            DataTable dt = Database.returnDataTable(sql);
            string[] dsDethi = null;
            if (dt != null && dt.Rows.Count > 0)
                dsDethi = dt.Rows[0]["dsDeThi"].ToString().Split(',');
            List<int> temp = null;
            if (dsDethi != null && dsDethi.Length > 0)
                foreach (string item in dsDethi) if (!string.IsNullOrEmpty(item)) { temp.Add(int.Parse(item)); }
            Random r = new Random();
            int index = r.Next(temp.Count);
            int dethi = temp[index];
            List<tblCauHoiThi> list = new List<tblCauHoiThi>();
            List<tblCauHoiThi> listtemp = new List<tblCauHoiThi>();
            Hashtable hash = new Hashtable();
            hash["idDeThi"] = dethi;
            hash["idSinhVien"] = idSinhvien;
            hash["idToChucThi"] = tochucthiid;
            if (Database.ExecuteInsert(hash, "tblKetQuaThi") > 0)
            {
                sql = String.Format("SELECT * FROM tblCauHoiThi WHERE stt = '{0}'", dethi);
                dt = Database.returnDataTable(sql);
                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        int stt = int.Parse(row["stt"].ToString());
                        int idDeThi = int.Parse(row["idDeThi"].ToString());
                        int idMH = int.Parse(row["idMH"].ToString());
                        int sodiem = int.Parse(row["sodiem"].ToString());
                        string dsLoaiCauHoi = row["dsLoaiCauHoi"].ToString();
                        string img = row["img"].ToString();
                        string media = row["media"].ToString();
                        string noidung = row["noidung"].ToString();
                        string cauTraLoi = row["cauTraLoi"].ToString();
                        int idNHCH = int.Parse(row["idNHCH"].ToString());
                        List<tblDapAnThi> listdapan = new List<tblDapAnThi>();
                        sql = String.Format("SELECT * FROM tblDapAnThi WHERE idCauHoiThi = '{0}'", stt);
                        DataTable dtCTL = Database.returnDataTable(sql);
                        if (dtCTL != null && dtCTL.Rows.Count > 0)
                        {
                            foreach (DataRow subrow in dtCTL.Rows)
                            {
                                int sstt = int.Parse(subrow["stt"].ToString());
                                int idCauHoiThi = int.Parse(subrow["idCauHoiThi"].ToString());
                                string simg = subrow["img"].ToString();
                                string smedia = subrow["media"].ToString();
                                string snoidung = subrow["noidung"].ToString();
                                tblDapAnThi dp = new tblDapAnThi(sstt, idCauHoiThi, simg, smedia, snoidung);
                                listdapan.Add(dp);
                            }
                            List<tblDapAnThi> listdapanrand = new List<tblDapAnThi>();
                            Random ra = new Random();
                            listdapanrand = listdapan.OrderBy(c => ra.Next()).ToList();
                            tblCauHoiThi bean = new tblCauHoiThi(stt, idDeThi, idMH, sodiem, dsLoaiCauHoi, img, media, noidung, cauTraLoi, idNHCH, listdapanrand);
                            list.Add(bean);
                        }
                    }
                }
            }
            if (listtemp.Count > 0)
            {
                Random rad = new Random();
                list = listtemp.OrderBy(c => rad.Next()).ToList();
            }
            return list;
        }
        /// <summmary>
        /// DAO for tblBaiThi
        /// </summary>
        public List<tblBaiThi> gettblBaiThi(string ketquathiid)
        {
            string sql;
            sql = String.Format("SELECT * FROM tblDeThi WHERE idKQT = '{0}'", ketquathiid);
            List<tblBaiThi> list = new List<tblBaiThi>();
            DataTable dt = Database.returnDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    int stt = int.Parse(row["stt"].ToString());
                    int idKQT = int.Parse(row["idKQT"].ToString());
                    int idCHT = int.Parse(row["idCHT"].ToString());
                    string dapancuoicung = row["dapancuoicung"].ToString();
                    string noidung = row["noidung"].ToString();
                    tblBaiThi bean = new tblBaiThi(stt, idKQT, idCHT, dapancuoicung, noidung);
                    list.Add(bean);
                }
            }
            return list;
        }
        /// <summmary>
        /// DAO for Lay list mon thi trong ngay
        /// </summary>
        public List<tblToChucThi> getMonThi(int idSV)
        {
            string sql;
            sql = String.Format("Select B.* from tblDangKyLop A, tblToChucThi B "
                + "WHERE A.idLH = B.idLopHoc AND DATEDIFF(Day, B.ngaythi, getdate()) = 0 "
                + " AND A.idSV = '{0}'", idSV);
            DataTable dt = Database.returnDataTable(sql);
            string[] dsDethi = null;
            List<tblToChucThi> list = new List<tblToChucThi>();
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow prow in dt.Rows)
                {
                    dsDethi = dt.Rows[0]["dsDeThi"].ToString().Split(',');
                    int stt = int.Parse(prow["stt"].ToString());
                    int idLopHoc = int.Parse(prow["idLopHoc"].ToString());
                    string dsDeThi = prow["dsDeThi"].ToString();
                    DateTime ngaythi = (DateTime)(prow["ngaythi"]);
                    string giothi = prow["giothi"].ToString();
                    string dsGiamKhao = prow["dsGiamKhao"].ToString();
                    string dsGiaoVienChamThi = prow["dsGiaoVienChamThi"].ToString();
                    int idKyThi = int.Parse(prow["idKyThi"].ToString());
                    int timethi = 0;

                    List<int> temp = new List<int>();
                    if (dsDethi != null && dsDethi.Length > 0)
                        foreach (string item in dsDethi) if (!string.IsNullOrEmpty(item)) { temp.Add(int.Parse(item)); }
                    if (temp.Count > 0)
                    {
                        int iddethi = temp[0];
                        sql = string.Format("SELECT * FROM tblDeThi WHERE stt = '{0}'", iddethi);
                        dt = Database.returnDataTable(sql);
                        tblDeThi b = null;
                        if (dt != null && dt.Rows.Count > 0)
                        {
                            foreach (DataRow row in dt.Rows)
                            {
                                int sstt = int.Parse(row["stt"].ToString());
                                string tenMH = row["tenMH"].ToString();
                                int idGiangVien = int.Parse(row["idGiangVien"].ToString());
                                string tenDeThi = row["tenDeThi"].ToString();
                                string thoigianlambai = row["thoigianlambai"].ToString();
                                int soluongcauhoi = int.Parse(row["soluongcauhoi"].ToString());
                                string diemdat = row["diemdat"].ToString();
                                string diemtoida = row["diemtoida"].ToString();
                                string ghichu = row["ghichu"].ToString();
                                string ghichusaukhithi = row["ghichusaukhithi"].ToString();
                                DateTime ngaytao = (DateTime)(row["ngaytao"]);
                                b = new tblDeThi(sstt, tenMH, idGiangVien, tenDeThi, thoigianlambai, soluongcauhoi, diemdat, diemtoida, ghichu, ghichusaukhithi, ngaytao);
                                timethi = int.Parse(b.Thoigianlambai);
                            }
                        }
                        string status = "Vào thi";
                        string time1 = DateTime.Parse(prow["giothi"].ToString()).ToString("t");
                        string time2 = DateTime.Now.ToString("t");

                        TimeSpan c = DateTime.Parse(time2).Subtract(DateTime.Parse(time1));
                        if (c.TotalMinutes < 0) status = "Chưa thi";
                        else if (c.TotalMinutes <= 10) status = "Vào thi";
                        else if (c.TotalMinutes < timethi) status = "Đang thi";
                        else status = "Đã thi";

                        tblToChucThi bean = new tblToChucThi(stt, idLopHoc, dsDeThi, ngaythi, giothi, dsGiamKhao, dsGiaoVienChamThi, idKyThi, b, status);
                        list.Add(bean);
                    }
                }
            }
            return list;
        }
    }
}
